package com.dc.dao;

import com.dc.po.Blog;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface BlogRepository extends JpaRepository<Blog,Long>, JpaSpecificationExecutor<Blog> {

    Page<Blog> findAllByPublishedIsTrue(Pageable pageable);

    /*根据分页条件查询推荐文章集合*/
    @Query("select b from Blog b where b.recommend = true")
    List<Blog> findTop(Pageable pageable);

    /*根据title、content模糊查询文章集合  ?1 参数1  */
    @Query("select b from Blog b where b.title like ?1 or b.content like ?1")
    Page<Blog> findByQuery(String query, Pageable pageable);


    @Transactional
    @Modifying //修改必须指定注解跟保持事务操作
    @Query("update Blog b set b.views = b.views+1 where b.id = ?1")
    int updateViews(Long id);

    //查询博客更新年份集合 2021,2020,2019...
    //select date_format(b.update_time,'%Y')as year from t_blog b group by year order by year desc
    @Query("select function('date_format',b.updateTime,'%Y') as year from Blog b group by function('date_format',b.updateTime,'%Y')order by year desc")
    List<String> findGroupYear();

    @Query(value = "select date_format(b.update_time,'%Y')as year from blog.t_blog b group by year order by year desc",nativeQuery = true)
    List<String> findGroupYear1();

    //按年份查询博客列表(只查询公开的博客)
    //select * from t_blog b where date_format(b.update_time,'%Y') = '2021' order by b.update_time desc
    @Query("select b from Blog b where b.published=true and function('date_format',b.updateTime,'%Y') = ?1 order by b.updateTime desc" )
    List<Blog> findByYear(String year);

    @Query(value = "select * from blog.t_blog b where date_format(b.update_time,'%Y') = '2021' order by b.update_time desc",nativeQuery = true)
    List<Blog> findByYear1(String year);

    //根据用户id查询用户博客
    List<Blog> findByUserId(Long user_id);

}
